package com.uniview.plugins.epms;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.uniview.entity.vo.Pager;
import com.uniview.entity.vo.orderType.ProjectDelivery;
import com.uniview.plugins.adapter.EPMSDataSource;

@Service("epmsService")
public class EPMSService {
	
	private Logger logger  = Logger.getLogger(EPMSService.class);

	@Autowired
	private EPMSDataSource epmsDataSource;

	@Value("#{configProperties['epms.table_projcetsupport']}")
	private String tableProjectSupport;

	@Value("#{configProperties['epms.table_userinfo']}")
	private String tableUserInfo;

	// 插入表记录
	public void saveProjectSupport(ProjectSupport support) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String startDtStr = sdf.format(support.getStartDt());
		String endDtStr = sdf.format(support.getEndDt());
		String addTimeStr = sdf.format(new Date());
		String sql = "insert into " + tableProjectSupport
				+ " (projectId,startDt, endDt, supportUser, addUser, addTime, days, supportType, supportPrice, supportContent) "
				+ "values ('" + support.getProjectId() + "', CONVERT(dateTime, '" + startDtStr
				+ "', 20), CONVERT(dateTime, '" + endDtStr + "', 20), " + support.getSupportUser() + ", "
				+ support.getSupportUser() + ", CONVERT(dateTime, '" + addTimeStr + "', 20), " + support.getDays()
				+ ", " + support.getSupportType() + ", " + support.getSupportPrice() + ", '"
				+ support.getSupportContent() + "')";
		epmsDataSource.update(sql);
	}

	// 根据工号查用户信息
	public Integer findUserInfoByAccount(String account) {
		String sql = "select u.id from " + this.tableUserInfo + " u where upper(u.account)=upper('" + account + "')";
		return (Integer) epmsDataSource.queryForObject(sql, "id");
	}

	/**
	 * 根据用户账号查询用户信息
	 *
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public UserInfo queryUserInfoById(String id) throws Exception {
		String sql = "select * from "+ this.tableUserInfo +" where upper(account)=upper('" + id + "')";
		logger.info(sql);
		return epmsDataSource.query(UserInfo.class, sql);
	}

	/**
	 * 根据用户权限获取查询条件 角色： （办事处工程师、项目经理、售中接口人、服务经理：本办事处所有项目 技服片总、区域售中：本区域的办事处的项目
	 * 项目交付部：所有项目 销售、SE：与自己相关的项目 办事处主任：本办事处所有项目）
	 * 
	 * @param userInfo
	 * @return
	 */
	private StringBuilder getQueryConditionByRole(UserInfo userInfo) {
		StringBuilder query = new StringBuilder();
		String role = userInfo.getRoleName();
		if (role != null) {
			if (role.contains("总部") || role.contains("管理员") || role.contains("工程管理部")) {

			} else if (role.contains("技服片区主管") || role.contains("片区售中")) {
				query.append(" AND officeName in (");
				List<Map<String, Object>> list = epmsDataSource
						.queryForList("select officeName from vOffice where areaName in ("
								+ "select areaName from vArea where manager='" + userInfo.getId() + "')");
				if (list.size() > 0) {
					for (Map<String, Object> map : list) {
						query.append("'" + map.get("OFFICENAME") + "',");
					}
					query.deleteCharAt(query.lastIndexOf(","));
					query.append(")");
				} else {
					query.setLength(0);
					query.append(" or  account='" + userInfo.getAccount() + "'");
				}
			} else if (role.contains("服务经理") || role.contains("办事处工程师") || role.contains("项目经理")
					|| role.contains("售中接口人") || role.contains("办事处售中经理")) {
				query.append(" AND officeName in (");
				// 两种查询方式
				List<Map<String, Object>> list = epmsDataSource
						.queryForList("select officeName from vOffice where serverManager='" + userInfo.getId() + "'");
				List<Map<String, Object>> list1 = epmsDataSource
						.queryForList("select officeName from vOffice where id='" + userInfo.getOffice() + "'");
				list1.addAll(list);
				for (Map<String, Object> map : list1) {
					query.append("'" + map.get("OFFICENAME") + "',");
				}
				if (list1.size() > 0) {
					query.deleteCharAt(query.lastIndexOf(","));
					query.append(")");
				} else {
					query.setLength(0);
					query.append(" AND  account='" + userInfo.getAccount() + "'");
				}
			} else {// 工程师、个人
				query.append(" AND  account='" + userInfo.getAccount() + "'");
			}
		}
		return query;
	}
	
	/**
     * 项目交付查询，根据用户角色查询
     *
     * @param pageSize
     * @param pageNo
     * @return
     */
    public Pager<ProjectDelivery> seniorSearchProject(UserInfo userInfo, String queryString, int pageSize, int pageNo) throws Exception {

        StringBuilder query = new StringBuilder();

        if (StringUtils.isNotBlank(queryString)) {
            query.append(" AND ( projectName like '%" + queryString + "%' ");
            query.append(" or ContactAddress like '%" + queryString + "%' ");
            query.append(" or ContactPerson like '%" + queryString + "%' )");
        }

        StringBuilder countSql = new StringBuilder();
        StringBuilder queryCondition = getQueryConditionByRole(userInfo);
        countSql.append("select count(1) from vProjectInf_EPMS w1 where 1=1 ");
        countSql.append(query);
        countSql.append(queryCondition);
        logger.info(countSql);
        Integer count = epmsDataSource.getCount(countSql.toString());
        Pager<ProjectDelivery> pager = new Pager<ProjectDelivery>();

        if (count > 0) {
            StringBuilder sql = new StringBuilder();
            String sqlStr = query.append(queryCondition).toString();
            sql.append("SELECT w2.n, w1.* FROM vProjectInf_EPMS w1," +
                    "(SELECT TOP " + pageSize * pageNo + " row_number() OVER (order by projectName) n, projectId FROM vProjectInf_EPMS where 1=1 " + sqlStr + ") w2 " +
                    "WHERE w1.projectId = w2.projectId AND w2.n >" + pageSize * (pageNo - 1) +"");
            logger.info(sql);
            List<ProjectDelivery> projectLists = epmsDataSource.queryForList(ProjectDelivery.class, sql.toString());

            pager.setPagerList(projectLists);
            pager.setTotalRow(count);
        }

        return pager;
    }
}
